<?php 
// KGB tables 
// develeoper 1: marko zorec 
// email: mzorec@gmail.com

// Create tables



$sql ="CREATE TABLE KGB_activites (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
deletedtime INT,
PRIMARY KEY(id)
)engine=innodb";
mysql_query($sql,$con);
 
$sql = "CREATE TABLE KGB_regions (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
PRIMARY KEY(id)
)engine=innodb";  
mysql_query($sql,$con);

$sql = "CREATE TABLE kgb_town (
  id int(11) NOT NULL AUTO_INCREMENT,
  regionid int(11) NOT NULL,
  name varchar(40) NOT NULL,
  PRIMARY KEY (id),
  KEY kgb_towns_fk (regionid),
  CONSTRAINT kgb_towns_fk FOREIGN KEY (regionid) REFERENCES kgb_regions (id)
) ENGINE=InnoDB"; 
mysql_query($sql,$con);

$sql = "ALTER TABLE `kgb_towns` ADD FOREIGN KEY (`regionid`) REFERENCES `kgb`.`kgb_regions`(`id`) ON UPDATE CASCADE;";
mysql_query($sql,$con);
 
$sql ="CREATE TABLE KGB_user_act (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
location VARCHAR(40),
telephone VARCHAR(40),
www VARCHAR(40),
description TEXT,
price VARCHAR(20),
deletedtime INT,
actid INT NOT NULL,
townid INT NOT NULL,
userid INT NOT NULL,
ac INT, 
ag INT, 
PRIMARY KEY(id)
)engine=innodb";
mysql_query($sql,$con);

$sql = "ALTER TABLE `kgb_user_act` ADD FOREIGN KEY (`actid`) REFERENCES `kgb`.`kgb_activites`(`id`) ON UPDATE CASCADE;";
mysql_query($sql,$con);

$sql = "ALTER TABLE `kgb_user_act` ADD FOREIGN KEY (`townid`) REFERENCES `kgb`.`kgb_towns`(`id`) ON UPDATE CASCADE;";
mysql_query($sql,$con);

$sql = "ALTER TABLE `kgb_user_act` ADD FOREIGN KEY (`userid`) REFERENCES `kgb`.`kgb_users`(`id`) ON UPDATE CASCADE;";
mysql_query($sql,$con);

$sql ="CREATE TABLE KGB_packages (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
description TEXT,
price VARCHAR(20),
PRIMARY KEY(id)
)engine=innodb";
 mysql_query($sql,$con);

$sql ="CREATE TABLE `kgb_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) NOT NULL,
  `password` varchar(40) NOT NULL,
  `pwd_salt` varchar(40) NOT NULL,
  `email` varchar(70) NOT NULL,
  `rank` int(11) NOT NULL,
  `act_key` int(11) DEFAULT NULL,
  `is_active` int(11) NOT NULL,
  `packageid` int(11) NOT NULL,
  `date_reg` int(11) NOT NULL,
  `exp_date` int(11) NOT NULL,
  `deleted_time` int(11) DEFAULT NULL,
  `login_flood` text,
  PRIMARY KEY (`id`),
  KEY `packageid` (`packageid`),
  CONSTRAINT `fk1_users` FOREIGN KEY (`packageid`) REFERENCES `kgb`.`kgb_packages` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1";
mysql_query($sql,$con);

$sql ="CREATE TABLE KGB_grades (
id INT(11) NOT NULL AUTO_INCREMENT,
uaid INT NOT NULL,
grade INT,
PRIMARY KEY(id)
)engine=innodb";
mysql_query($sql,$con);

$sql = "ALTER TABLE `kgb_grades` ADD FOREIGN KEY (`uaid`) REFERENCES `kgb`.`kgb_user_act`(`id`) ON UPDATE CASCADE;";
mysql_query($sql,$con);

$sql ="CREATE TABLE KGB_comments (
id INT(11) NOT NULL AUTO_INCREMENT,
uaid INT NOT NULL,
authorname VARCHAR(40) NOT NULL,
comment TEXT,
deletedtime INT,
PRIMARY KEY(id)
)engine=innodb";
 mysql_query($sql,$con);

$sql = "ALTER TABLE `kgb_comments` ADD FOREIGN KEY (`uaid`) REFERENCES `kgb`.`kgb_user_act`(`id`) ON UPDATE CASCADE;";
mysql_query($sql,$con); 

$sql ="CREATE TABLE KGB_messages (
id INT(11) NOT NULL AUTO_INCREMENT,
reciverid INT NOT NULL,
senderid VARCHAR(20) NOT NULL,
comment TEXT,
if_reg INT NOT NULL,
deletedtime INT,
PRIMARY KEY(id)
)engine=innodb"; 
mysql_query($sql,$con);
?> 
